import os
import sys
localDir = os.path.dirname(__file__)
curpath = os.path.normpath(os.path.join(os.getcwd(), localDir))
grandparent = os.path.normpath(os.path.join(curpath, '../'))
sys.path.insert(0, grandparent)

import unittest

from lib.create_table import CreateTable

from databases.mysql import Mysql
from databases.sqlite import Sqlite
from databases.db2 import Db2
from databases.mssql import Mssql
from databases.oracle import Oracle
from databases.postgres import Postgres

config = {"development" : {"db" : "sqlite:///:memory:"}}

class TestCreateTable(unittest.TestCase):

    def testTruth(self):
        self.assertTrue(True)

    def testInitialiazeMysql(self):
        alc = Mysql(config['development']['db'])
        query = """users, engine : InnoDB, char_set : latin1, comment : some comment
    column: id,             type : int, length : 11, auto : true, not_null : true
    column: first_name,     type : varchar, length : 100, default : NULL
    column: last_name,      type : varchar, length : 100, default : NULL
     column: updated_at, type : datetime, default : NULL
    column: created_at, type : datetime, default : NULL
    primary_key: id, first_name"""
        sql = CreateTable(alc, query)

        self.assertEqual("users", sql.table)
        self.assertEqual("CREATE TABLE IF NOT EXISTS users ( id INT(11)  NOT NULL AUTO_INCREMENT,first_name VARCHAR(100)  DEFAULT NULL,last_name VARCHAR(100)  DEFAULT NULL,updated_at DATETIME  DEFAULT NULL,created_at DATETIME  DEFAULT NULL , PRIMARY KEY (id, first_name) ) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1 COMMENT = 'some comment';", sql.query)

    def testInitialiazeSqlite(self):
        alc = Sqlite(config['development']['db'])
        query = """users, engine : InnoDB, char_set : latin1, comment : some comment
    column: id,             type : INTEGER
    column: first_name,     type : varchar, length : 100, default : NULL
    column: last_name,      type : varchar, length : 100, default : NULL
     column: updated_at, type : datetime, default : NULL
    column: created_at, type : datetime, default : NULL
    primary_key: id, first_name"""
        sql = CreateTable(alc, query)

        self.assertEqual("users", sql.table)
        self.assertEqual("CREATE TABLE IF NOT EXISTS users ( id INTEGER ,first_name VARCHAR(100)  DEFAULT NULL,last_name VARCHAR(100)  DEFAULT NULL,updated_at DATETIME  DEFAULT NULL,created_at DATETIME  DEFAULT NULL , PRIMARY KEY (id, first_name) );", sql.query)

    def testInitialiazeDb2(self):
        alc = Db2(config['development']['db'])
        query = """users, engine : InnoDB, char_set : latin1, comment : some comment
    column: id,             type : INTEGER, auto : true
    column: first_name,     type : varchar, length : 100, default : , not_null : true
    column: last_name,      type : varchar, length : 100, default : 
     column: updated_at, type : date, default : NULL
    column: created_at, type : date, default : NULL
    primary_key: id, first_name"""
        sql = CreateTable(alc, query)

        self.assertEqual("users", sql.table)
        self.assertEqual("CREATE TABLE users ( id INTEGER  GENERATED ALWAYS AS IDENTITY,first_name VARCHAR(100)  NOT NULL DEFAULT '',last_name VARCHAR(100)  DEFAULT '',updated_at DATE  DEFAULT NULL,created_at DATE  DEFAULT NULL , PRIMARY KEY (id, first_name) );", sql.query)

    def testInitialiazeMssql(self):
        alc = Mssql(config['development']['db'])
        query = """users, engine : InnoDB, char_set : latin1, comment : some comment
    column: id,             type : INTEGER, auto : true
    column: first_name,     type : varchar, length : 100, default : , not_null : true
    column: last_name,      type : varchar, length : 100, default :
     column: updated_at, type : date, default : NULL
    column: created_at, type : date, default : NULL
    primary_key: id, first_name"""
        sql = CreateTable(alc, query)

        self.assertEqual("users", sql.table)
        self.assertEqual("CREATE TABLE users ( id INTEGER  IDENTITY,first_name VARCHAR(100)  NOT NULL DEFAULT '',last_name VARCHAR(100)  DEFAULT '',updated_at DATE  DEFAULT NULL,created_at DATE  DEFAULT NULL , CONSTRAINT users_pk PRIMARY KEY (id, first_name) );", sql.query)

    def testInitialiazeOracle(self):
        alc = Oracle(config['development']['db'])
        query = """users, engine : InnoDB, char_set : latin1, comment : some comment
    column: id,             type : INTEGER, auto : true
    column: first_name,     type : varchar, length : 100, default : , not_null : true
    column: last_name,      type : varchar, length : 100, default :
     column: updated_at, type : date, default : NULL
    column: created_at, type : date, default : NULL
    primary_key: id, first_name"""
        sql = CreateTable(alc, query)

        self.assertEqual("users", sql.table)
        self.assertEqual("CREATE TABLE users ( id INTEGER ,first_name VARCHAR(100)  NOT NULL DEFAULT '',last_name VARCHAR(100)  DEFAULT '',updated_at DATE  DEFAULT NULL,created_at DATE  DEFAULT NULL , CONSTRAINT users_pk PRIMARY KEY (id, first_name) );", sql.query)

    def testInitialiazePostgres(self):
        alc = Postgres(config['development']['db'])
        query = """users, engine : InnoDB, char_set : latin1, comment : some comment
    column: id,             type : INTEGER, sequence : sq
    column: first_name,     type : varchar, length : 100, default : , not_null : true
    column: last_name,      type : varchar, length : 100, default :
     column: updated_at, type : date, default : NULL
    column: created_at, type : date, default : NULL
    primary_key: id"""
        sql = CreateTable(alc, query)

        self.assertEqual("users", sql.table)
        self.assertEqual("CREATE TABLE users ( id INTEGER  NEXTVAL('sq'),first_name VARCHAR(100)  NOT NULL DEFAULT '',last_name VARCHAR(100)  DEFAULT '',updated_at DATE  DEFAULT NULL,created_at DATE  DEFAULT NULL , PRIMARY KEY (id) );", sql.query)

    def testInitialiazePostgresDoubleKey(self):
        alc = Postgres(config['development']['db'])
        query = """users, engine : InnoDB, char_set : latin1, comment : some comment
    column: id,             type : INTEGER, sequence : sq
    column: first_name,     type : varchar, length : 100, default : , not_null : true
    column: last_name,      type : varchar, length : 100, default :
     column: updated_at, type : date, default : NULL
    column: created_at, type : date, default : NULL
    primary_key: id, first_name"""
        sql = CreateTable(alc, query)

        self.assertEqual("users", sql.table)
        self.assertEqual("CREATE TABLE users ( id INTEGER  NEXTVAL('sq'),first_name VARCHAR(100)  NOT NULL DEFAULT '',last_name VARCHAR(100)  DEFAULT '',updated_at DATE  DEFAULT NULL,created_at DATE  DEFAULT NULL , CONSTRAINT users_pk PRIMARY KEY (id, first_name) );", sql.query)

    def testExecute(self):
        alc = Sqlite(config['development']['db'])
        query = """users, engine : InnoDB, char_set : latin1, comment : some comment
    column: id,             type : INTEGER
    column: first_name,     type : varchar, length : 100, default : NULL
    column: last_name,      type : varchar, length : 100, default : NULL
     column: updated_at, type : datetime, default : NULL
    column: created_at, type : datetime, default : NULL
    primary_key: id, first_name"""
        sql = CreateTable(alc, query)

        self.assertTrue(sql.execute())

if __name__ == "__main__":
    unittest.main()
